<?php
// +----------------------------------------------------------------------
// | PHP 数据库操作类
// +----------------------------------------------------------------------
// | Copyright (c) 2015 www.cntaige.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
// +----------------------------------------------------------------------
// | Author: CnTaiGe <hst042@163.com>
// +----------------------------------------------------------------------
// | QQ: 574868786
// +----------------------------------------------------------------------
// | Date: 2015-2-26
// +----------------------------------------------------------------------
/**
 * 数据库操作类
 */
class DB
{
    private $pdo;
    private $sql; // sql语句
    private $table; // 表名

    public function __construct(array $dbConfig = array(), $table = '')
    {
        $dbArr = array_merge(array(
            'DB_HOST' => '127.0.0.1',
            'DB_NAME' => '',
            'DB_PORT' => 3306,
            'DB_USER' => 'root',
            'DB_PWD' => '',
            'CHARSET' => 'utf8'
        ), $dbConfig);
        $this->table = $table;
        $this->pdo = new PDO ("mysql:host=" . $dbArr ['DB_HOST'] . ';dbname=' . $dbArr ['DB_NAME'] . ';port=' . $dbArr ['DB_PORT'], $dbArr ['DB_USER'], $dbArr ['DB_PWD'], array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND => 'set names ' . $dbArr ['CHARSET']
        ));
        $this->pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }

    /**
     * 批量插入数据
     * @param array $datas
     * @return int 返回影响的行数
     */
    public function insertMore(array $datas)
    {
        $fields = [];
        foreach ($datas[0] as $k => $v) {
            $fields[] = $k;
        }
        $this->sql = "insert into {$this->table} (" . implode(',', $fields) . ") values ";
        $fieldValues = [];
        $fieldBinds = [];
        foreach ($datas as $key => $vk) {
            $bins = [];
            foreach ($vk as $fk => $fv) {
                $bins[] = ':' . $fk . $key;
                $fieldBinds[':' . $fk . $key] = $fv;
            }
            $fieldValues[] = '(' . implode(',', $bins) . ')';
        }
        $this->sql .= implode(',', $fieldValues);
        return self::prepareValue($fieldBinds)->rowCount();
    }

    /**
     * 执行sql语句
     * @param $sql
     * @param array $value 用于占位符？或 ':name'=>value
     * @return int 受影响的行数
     */
    public function execute($sql, array $value = array())
    {
        $this->sql = $sql;
        return self::prepareValue($value)->rowCount();
    }

    /**
     * 启动事务
     * @return bool
     */
    public function begintrans()
    {
        if ($this->pdo->inTransaction()) $this->pdo->commit();
        return $this->pdo->beginTransaction();
    }

    /**
     * 提交事务
     * @return bool
     */
    public function commit()
    {
        return $this->pdo->commit();
    }

    /**
     * 回滚事务
     * @return bool
     */
    public function rollback()
    {
        return $this->pdo->rollBack();
    }

    /**
     * 切换表名
     * @param $table
     * @return $this
     */
    public function table($table)
    {
        $this->table = $table;
        return $this;
    }

    /**
     * 查询数据
     *
     * @param string $sql
     *            完整的sql语句，安全预处理使用？代替参数
     * @param array $values
     *            替换占位符参数？的数据
     * @param callable $rowFn 按行获取回调函数
     * @return array 查询操作返回关联数组
     */
    public function rawQuery($sql, $values = array(), callable $rowFn = null)
    {
        $this->sql = $sql;
        $statement = !empty ($values) ? self::prepareValue($values) : $this->pdo->query($this->sql = $sql);
        $statement->setFetchMode(PDO::FETCH_ASSOC);
        if ($rowFn !== null) {
            while ($row = $statement->fetch()) {
                $rowFn($row);
            }
        } else {
            $resultArr = $statement->fetchAll();
            return $resultArr;
        }
    }

    /**
     * 删除数据
     *
     * @param array|string $where
     *            删除条件参数使用？代替，动态更换数据表$where['__table']=表名,$where['__string']=条件语句
     * @param array $values
     *            用于预处理替换where占位符？的值
     * @return number 返回影响的行数
     *
     */
    public function delete($where, $values = array())
    {
        if (empty ($where))
            return 0;

        $this->sql = "DELETE FROM " . (is_array($where) ? (empty ($where ['__table']) ? $this->table . ' WHERE ' . $where ['__string'] : $where ['__table'] . ' WHERE ' . $where ['__string']) : $this->table . ' WHERE ' . $where);
        $statement = self::prepareValue($values);
        return $statement->rowCount();
    }

    /**
     * 更新数据，为使查询更安全，参数使用占位符？代替
     *
     * @param array $array
     *            array('字段'=>'值')
     * @param string $where条件
     *            where语句使用参数时用？代替
     * @param array $values
     *            where语句中含占位符？则此数组值对应相应的？值
     * @return number 返回影响的行数
     */
    public function update($array, $where = '', $values = array())
    {
        $table = empty ($array ['__table']) ? $this->table : $array ['__table'];
        unset ($array ['__table']);
        $sets = implode(',', array_map(function ($v) {
            return strpos($v, '.') === false ? "`$v`=?" : $v . '=?';
        }, array_keys($array)));
        $this->sql = "UPDATE " . $table . " SET $sets WHERE " . $where;
        $statement = self::prepareValue(array_merge(array_values($array), $values));
        return $statement->rowCount();
    }

    /**
     * 插入数据
     *
     * @param array $array
     *            array('字段'=>'值'),动态更换数据表名$array['__table']=table
     * @return string 返回最后插入的主键或序列值
     *
     */
    public function insert($array)
    {
        $table = empty ($array ['__table']) ? $this->table : $array ['__table'];
        if (isset ($array ['__table']))
            unset ($array ['__table']);
        $this->sql = 'INSERT INTO ' . $table . '(' . implode(',', array_map(function ($v) {
                return strpos($v, '.') === false ? '`' . $v . '`' : $v; // 处理‘.’连接符
            }, array_keys($array))) . ') VALUES(' . rtrim(str_repeat('?,', count($array)), ',') . ')';
        self::prepareValue((array_values($array)));
        return $this->pdo->lastInsertId();
    }

    /**
     * 预处理
     *
     * @param array $value
     *            $value
     *            占位符？ 对应的数组值
     * @return PDOStatement
     */
    private function prepareValue($value)
    {
        $statement = $this->pdo->prepare($this->sql);
        $statement->execute($value);
        return $statement;
    }

    /**
     * 用于给字符加引号用于处理sql注入
     *
     * @param string $string
     *            要加引号的字符
     * @return string
     */
    public function quote($string)
    {
        return is_numeric($string) ? $string : $this->pdo->quote($string);
    }

    /**
     *
     * @return string 返回实例化时的数据表
     *
     */
    public function getTable()
    {
        return $this->table;
    }

    /**
     * 返回pdo操作实例
     * @return PDO
     */
    public function getPDO()
    {
        return $this->pdo;
    }

    /**
     * 返回最后一次查询的sql语句
     *
     * @return string
     */
    public function getSqlString()
    {
        return $this->sql;
    }

    public function __destruct()
    {
        $this->pdo = null;
    }
}